﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    public class ParameterService
    {
        //查询一个属性索引
        public static Model.ParameterIndex SelectIndexbyID(int id)
        {
            string sql = string.Format("SELECT * FROM ParameterIndex WHERE id = {0}",id);
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            Model.ParameterIndex p = new ParameterIndex(); ;
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    p.id = int.Parse(dr["id"].ToString());
                    p.value = dr["value"].ToString();
                }
                dr.Close();
            }
            dr.Close();
            return p;
        }



        //列出属性索引
        public static List<ParameterIndex> ListIndex()
        {
            string sql = "SELECT * FROM ParameterIndex";
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            List<ParameterIndex> list = new List<ParameterIndex>();
            if (dr.HasRows)
            {
                ParameterIndex p;
                while (dr.Read())
                {
                    p = new ParameterIndex();
                    p.id = int.Parse(dr["id"].ToString()); ;
                    p.value = dr["value"].ToString();
                    list.Add(p);
                }
                dr.Close();
            }
            dr.Close();
            return list;
        }
        /// <summary>
        /// 根据分类列出属性
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        //根据分类列出属性
        public static List<Parameter> GetParamList(string s)
        {

            string sql = string.Format("SELECT * FROM Parameter WHERE indexID = {0}", s);
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            List<Parameter> Ls = new List<Parameter>();
            if (dr.HasRows)
            {
                Parameter p;
                while (dr.Read())
                {
                    p = new Parameter();
                    p.id = int.Parse(dr["id"].ToString());
                    p.value = dr["value"].ToString();
                    p.indexID = int.Parse(dr["indexID"].ToString());
                    p.Pindexvalue = SelectIndexbyID(p.indexID);
                    Ls.Add(p);
                }
                dr.Close();
            }
            dr.Close();
            return Ls;
        }
        /// <summary>
        /// 列出所有属性
        /// </summary>
        /// <returns></returns>
        //列出所有属性
        public static List<Parameter> ListallParameter()
        {
            string sql = "SELECT * FROM Parameter";
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            List<Parameter> Ls = new List<Parameter>();
            if (dr.HasRows)
            {
                Parameter p;
                while (dr.Read())
                {
                    p = new Parameter();
                    p.id = int.Parse(dr["id"].ToString());
                    p.value = dr["value"].ToString();
                    p.indexID = int.Parse(dr["indexID"].ToString());
                    p.Pindexvalue = SelectIndexbyID(p.indexID);
                    Ls.Add(p);
                }
                dr.Close();
            }
            dr.Close();
            return Ls;
        }

        //修改属性
        //新增属性
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static bool Add(Model.Parameter model)
        {
            string sql = string.Format("INSERT into Parameter (value,indexID) VALUES ('{0}',{1})",model.value,model.indexID);
            int rows = SQLHelper.ExecuteNonQuery(SQLHelper.sqlConnectionString,CommandType.Text,sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //删除属性
        public static bool Del(int id)
        {
            string sql = string.Format("DELETE FROM Parameter WHERE id = {0}",id);
            int rows = SQLHelper.ExecuteNonQuery(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //查询一条属性
        public static Model.Parameter SelectOne(int id)
        {
            string sql = string.Format("SELECT * FROM Parameter WHERE id = {0}",id);
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            Model.Parameter p = new Parameter();;
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    p.id = int.Parse(dr["id"].ToString());
                    p.value = dr["value"].ToString();
                    p.indexID = int.Parse(dr["indexID"].ToString());
                }
                dr.Close();
            }
            dr.Close();
            return p;
        }

        //更新一条数据
        public static bool UpdateOne(Model.Parameter m)
        {
            string sql = string.Format("UPDATE Parameter SET value = '{0}',indexID = {1} WHERE id = {2}", m.value, m.indexID, m.id);
            int rows = SQLHelper.ExecuteNonQuery(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
